package com.beerandjesus.sportofkings.handicapper.dao;

import com.beerandjesus.sportofkings.handicapper.model.JockeyRating;
import com.beerandjesus.sportofkings.handicapper.model.JockeyRatingImpl;
import com.beerandjesus.sportofkings.handicapper.model.CramerJockeyRating;

import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Connection;
import java.util.List;
import java.util.ArrayList;

import org.apache.commons.lang.StringUtils;

/**
 * Date: Jul 7, 2012
 * Time: 7:48:22 PM
 */
public class JockeyDAOImpl extends AbstractDAOImpl implements JockeyDAO {

    private static final String JOCKEY_RATING = "jockey_rating";

    public void buildDatabase() throws DAOException {
        Connection connection = getConnectionManager().getJockeyDatabaseConnection();
        executeStatementIgnoringException(connection, "drop table " + JOCKEY_RATING);
        executeStatement(connection, "create table " + JOCKEY_RATING +
                " (name varchar(100), rating varchar(100))");

    }

    public int getRowCount() throws DAOException {
        Connection connection = getConnectionManager().getJockeyDatabaseConnection();
        return getRowCount(connection, JOCKEY_RATING);
    }

    public JockeyRating getJockeyRatingByName(String name) throws DAOException {
        Connection connection = getConnectionManager().getJockeyDatabaseConnection();
        JockeyRatingImpl rating = null;
        if (StringUtils.isEmpty(name)) {
            return rating;
        }
        PreparedStatement stmt = null;
        try {
            stmt = connection.prepareStatement("select * from " + JOCKEY_RATING + " where name = ?");
            stmt.setString(1, name.toLowerCase());
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                rating = new JockeyRatingImpl();
                rating.setJockeyName(name);
                CramerJockeyRating r = CramerJockeyRating.valueOf(rs.getString(2));
                rating.setRating(r);
            }
            return rating;
        } catch (SQLException e) {
            throw new DAOException("Error getting rating for jockey " + name, e);
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    // Ignore
                }
            }
        }
    }

    public void save(JockeyRating rating) throws DAOException {
        Connection connection = getConnectionManager().getJockeyDatabaseConnection();
        if (rating == null || rating.getJockeyName() == null || rating.getRating() == null) {
            throw new DAOException("JockeyRating cannot be null, and cannot have null values");
        }
        JockeyRating existing = getJockeyRatingByName(rating.getJockeyName());
        PreparedStatement stmt = null;
        String jName = rating.getJockeyName().toLowerCase();
        String rName = rating.getRating().name();
        try {
            if (existing != null) {
                stmt = connection.prepareStatement("update " + JOCKEY_RATING + " set rating = ?" +
                        " where name = ?");
                stmt.setString(1, rName);
                stmt.setString(2, jName);
            } else {
                stmt = connection.prepareStatement("insert into " + JOCKEY_RATING + " (name, rating) " +
                        "values (?,?)");
                stmt.setString(1, jName);
                stmt.setString(2, rName);
            }
            stmt.executeUpdate();
        } catch (SQLException e) {
            throw new DAOException("Error saving jockey rating " +
                    jName + "-" + rName, e);
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    // Ignore
                }
            }
        }

    }

    public List<JockeyRating> getAllJockeyRatings() throws DAOException {
        Connection conn = getConnectionManager().getJockeyDatabaseConnection();
        PreparedStatement stmt = null;
        List<JockeyRating> ratings = new ArrayList<JockeyRating>();
        try {
            stmt = conn.prepareStatement("select * from " + JOCKEY_RATING);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                JockeyRatingImpl jr = new JockeyRatingImpl();
                jr.setJockeyName(rs.getString("NAME"));
                jr.setRating(CramerJockeyRating.valueOf(rs.getString("RATING")));
                ratings.add(jr);
            }

        } catch (SQLException e) {
            throw new DAOException("Error getting all jockeys", e);
        } finally {
            if (stmt != null) {
                try {
                    stmt.close();
                } catch (SQLException e) {
                    // Ignore
                }
            }
        }
        return ratings;
    }
}
